﻿Imports MySql.Data.MySqlClient
Imports DTO
Imports GVariable.Variable
Imports System.Collections
Public Class CDataNhanVien
    Inherits DAO.CDataProvider
    Private strSQL As String

   
    Public Function DocBangNhanVien() As List(Of CNhanVien)
        Dim lNVien As New List(Of CNhanVien)
        strSQL = "select * from`qlbh`.`nhanvien` where `tinhTrang` = 1;"
        Dim tbNVien As DataTable
        tbNVien = ExecuteQuery_Table(strSQL)
        For i = 0 To tbNVien.Rows.Count - 1
            Dim nV As New CNhanVien(TaoNhanVien(tbNVien.Rows(i)))
            lNVien.Add(nV)
        Next

        Return lNVien
    End Function

    Public Function TaoNhanVien(ByVal tbRow As DataRow) As CNhanVien
        Dim nV As New CNhanVien()
        nV.maNV = tbRow("maNV").ToString()
        nV.hoTenNV = tbRow("hoTenNV").ToString()
        nV.maLoaiNV = Integer.Parse(tbRow("maLoaiNV"))
        nV.ngaySinh = Date.Parse(tbRow("ngaySinh"))
        nV.gioiTinh = tbRow("gioiTinh").ToString()
        nV.ngayVaoLam = Date.Parse(tbRow("ngayVaoLam"))
        nV.chucVu = tbRow("chucVu").ToString()
        nV.maQuyen = Integer.Parse(tbRow("maQuyen"))
        nV.hinh = tbRow("hinhNV").ToString()
        nV.dienThoai = tbRow("dThoai").ToString()
        nV.email = tbRow("email").ToString()
        nV.tinh = Integer.Parse(tbRow("maTinh"))
        nV.huyen = Integer.Parse(tbRow("maHuyen"))
        nV.ghiChu = tbRow("ghiChu").ToString()
        nV.tenDangNhap = tbRow("tenDangNhap").ToString()
        nV.matKhau = tbRow("matKhau").ToString()
        nV.tinhTrang = tbRow("tinhTrang").ToString()
        nV.luong = Double.Parse(tbRow("luong"))
        Return nV
    End Function

    Public Sub ThemSuaNhanVien(ByVal newNV As CNhanVien, ByVal flag As Integer)

        Dim _maNV As String = newNV.maNV
        Dim _hoTenNV As String = newNV.hoTenNV
        Dim _maLoaiNV As Integer = newNV.maLoaiNV
        Dim _ngaySinh As String = newNV.ngaySinh.ToString ' Lưu ý
        Dim _gioiTinh As String = newNV.gioiTinh
        Dim _chucVu As String = newNV.chucVu
        Dim _ngayVaoLam As String = newNV.ngayVaoLam.ToString() ' Lưu ý 
        Dim _maQuyen As Integer = newNV.maQuyen
        Dim _hinh As String = newNV.hinh
        Dim _dienThoai As String = newNV.dienThoai
        Dim _email As String = newNV.email
        Dim _tinh As Integer = newNV.tinh
        Dim _huyen As Integer = newNV.huyen
        Dim _ghiChu As String = newNV.ghiChu
        Dim _tenDangNhap As String = newNV.tenDangNhap
        Dim _matKhau As String = newNV.matKhau
        Dim _tinhTrang As Integer = newNV.tinhTrang
        Dim _luong As Double = newNV.luong
        '-----------------------------------------------
        'Câu lệnh insert dữ liệu vào bảng nhân viên--->nửa ngày cày :))

        If flag = 1 Then
            strSQL = "INSERT INTO `qlbh`.`nhanvien` (`maNV`, `hoTenNV`, `maLoaiNV`, `ngaySinh`," & _
            " `gioiTinh`, `ngayVaoLam`, `chucVu`, `maQuyen`, `hinhNV`, `dThoai`, `email`, `maTinh`, " & _
            "`maHuyen`, `ghiChu`, `tenDangNhap`, `matKhau`, `tinhTrang`, `luong`) " & _
            "VALUES ('" & _maNV & "', '" & _hoTenNV & "','" & _maLoaiNV & "', '" & _ngaySinh & "'," & _
            " '" & _gioiTinh & "', '" & _ngayVaoLam & "', '" & _chucVu & "', '" & _maQuyen & "', " & _
            "'" & _hinh & "', '" & _dienThoai & "', '" & _email & "','" & _tinh & "',' " & _huyen & "', " & _
            "'" & _ghiChu & "', '" & _tenDangNhap & "', '" & _matKhau & "', '" & _tinhTrang & "',' " & _luong & "');"

        Else
            'sqlstr = "UPDATE Employees SET  vFirstName  = '" & Fname & _
            '                            "', vLastname   = '" & Lname & _
            '                            "', cPhone      = '" & Phone & _
            '                            "',  cTitle      = '" & Title & _
            '                        "' WHERE vEmployeeID  = '" & EmpID & "'"
            strSQL = "UPDATE `qlbh`.`nhanvien` SET `hoTenNV`='" & _hoTenNV & _
                                                "', `maLoaiNV`='" & _maLoaiNV & _
                                                "', `ngaySinh`='" & _ngaySinh & _
                                                "', `gioiTinh`='" & _gioiTinh & _
                                                "', `ngayVaoLam`='" & _ngayVaoLam & _
                                                "', `chucVu`='" & _chucVu & _
                                                "', `maQuyen`= '" & _maQuyen & _
                                                "', `hinhNV`='" & _hinh & _
                                                "', `dThoai`='" & _dienThoai & _
                                                "', `email`= '" & _email & _
                                                "', `maTinh`='" & _tinh & _
                                                "', `maHuyen`='" & _huyen & _
                                                "', `ghiChu`='" & _ghiChu & _
                                                "', `tenDangNhap`='" & _tenDangNhap & _
                                                "', `matKhau`='" & _matKhau & _
                                                "', `tinhTrang`='" & _tinhTrang & _
                                                "', `luong`=' " & _luong & _
                                                "' WHERE `maNV`='" & _maNV & "';"
        End If
        'thuc hien cau truy van
        ExecuteNonQuery(strSQL)
    End Sub

    Public Sub XoaNhanVien(ByVal _maNV As String)
        strSQL = "UPDATE `qlbh`.`nhanvien` SET `tinhTrang`=0 WHERE `maNV`='" & _maNV & "';"
        ExecuteNonQuery(strSQL)
    End Sub

    Public Function TaoMaNhanVien(ByVal maNV As String, ByVal cot As String, ByVal bang As String, ByVal doDai As Integer) As String
        Return TaoMa(maNV, cot, bang, doDai)
    End Function

    Public Function TimKiem(ByVal maNV As String, ByVal tenNV As String) As List(Of CNhanVien)
        Dim dataTable As DataTable
        Try
            If (maNV <> "") Then
                strSQL = "select * from`qlbh`.`nhanvien` where `maNV` = '" & maNV & "' and `tinhTrang` = 1;"
            ElseIf tenNV <> "" And tenNV <> "Tất cả" Then
                strSQL = "select * from`qlbh`.`nhanvien` where `hoTenNV` like '%" & tenNV & "%' and `tinhTrang` = 1;"
            Else
                strSQL = "select * from`qlbh`.`nhanvien` where `tinhTrang` = 1;"
            End If
            dataTable = ExecuteQuery_Table(strSQL)
        Catch ex As Exception

        End Try
       
        Return ChuyenDataTableThanhList(dataTable)
    End Function

    Public Function ChuyenDataTableThanhList(ByVal dt As DataTable) As List(Of CNhanVien)
        Dim listNV As New List(Of CNhanVien)
        Try
            For i = 0 To dt.Rows.Count - 1
                Dim nV As New CNhanVien(TaoNhanVien(dt.Rows(i)))
                listNV.Add(nV)
            Next
        Catch ex As Exception

        End Try
        
        Return listNV
    End Function

    Public Function LayMaNV(ByVal tenDN As String, ByVal pass As String) As String
        Dim maNV As String = ""
        strSQL = "select `maNV` from`qlbh`.`nhanvien` where `tenDangNhap` like '" & tenDN & "' and `matKhau` like '" & pass & "';"
        MoKetNoi()
        If (intKiemTraKetNoi = 1) Then
            Try
                command = New MySqlCommand(strSQL, connection)
                command.CommandType = CommandType.Text
                maNV = command.ExecuteScalar().ToString()
                intKTTruyVan = 1
            Catch ex As Exception
                intKTTruyVan = 0
            End Try
            
            DongKetNoi()
        End If
        Return maNV
    End Function

    Public Function LayQuyen(ByVal maNV As String) As List(Of Integer)
        Dim lQuyen As New List(Of Integer)
        strSQL = "select `q`.`maQuyen`,`banHang`,`khachHang`,`sanPham`,`nhanVien`,`baoCao`,`heThong`  from `qlbh`.`phanquyen` as q, `qlbh`.`nhanvien` as nv where nv.`maNV` = '" & maNV & "' and q.`maQuyen` = nv.`maQuyen`;"
        Try
            Dim dt As DataTable = ExecuteQuery_Table(strSQL)
            ''For i = 0 To dt.Columns.Count - 1
            ''    lQuyen.Add(dt.Rows(0)(i))
            ''Next
            Dim dtQuyen As DataTable = dt

            Dim myRow As DataRow
            Dim myCol As DataColumn

            For Each myRow In dtQuyen.Rows
                For Each myCol In dtQuyen.Columns
                    lQuyen.Add(myRow(myCol))
                Next
                'Console.WriteLine()

            Next
        Catch ex As Exception
            intKTTruyVan = 0
        End Try
        
        Return lQuyen
    End Function
End Class
